﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;

namespace ProfilerPDS {
    class DataLayer {

        public DataLayer() {

        }

        private SQLiteConnection connect() {
            SQLiteConnection conn = new SQLiteConnection(@"data source=...\Debug\database.db");//if not exist, create a db with this name
            conn.Open();
            return conn;
        }

        private int disconnect(SQLiteConnection sqlConn) {
            sqlConn.Close();
            return 0;
        }

        public List<EventInfo> getEvents(int id) {

            string queryStr = "";
            queryStr += "select eventName,idThread,nameThread,idFunction,nameFunction,idClass,idObject, stackDepth, exceptionName,static,timestamp,IdApplication, evt.id ";
            queryStr += "from events evt,  eventsType evtT ";
            queryStr += "where evtT.id=evt.eventId "; 
            if(id==-1){
                //retrieve the last profiled application
                //queryStr+=  "select eventName, idThread,nameThread,idFunction,nameFunction,idClass,idObject, stackDepth, exceptionName,static,timestamp,IdApplication, evt.id";
                //queryStr+=  "from events evt,  eventsType evtT"; 
                //queryStr+=  "where evtT.id=evt.eventId"; 
                queryStr+=  "AND evt.IdApplication in (Select id  from applications where time in (select max(time) from applications)) ";
            }else{
                //retrieve the info related the specified application
                //queryStr =  "select eventName, idThread,nameThread,idFunction,nameFunction,idClass,idObject, stackDepth, exceptionName,static,timestamp,IdApplication";
                //queryStr+=  "from events evt,  eventsType evtT"; 
                //queryStr+=  "where evtT.id=evt.eventId"; 
                queryStr+=  "AND evt.IdApplication="+id;
                //queryStr+=  "order by timestamp desc"; //TODO maybe better order by evt.it asc
            }
            queryStr += " order by evt.id asc";
                
    
            List<EventInfo> eventInfoList= new List<EventInfo>();
            SQLiteConnection connection = connect();
            SQLiteCommand cmd = new SQLiteCommand(queryStr, connection);
            SQLiteDataReader rdr = cmd.ExecuteReader();
            
            EventInfo evtI = null;
            string eventName = null;

            try {
                while (rdr.Read()) {
                    //eventId|idThread|nameThread|idFunction|nameFunction|idClass|idObject|stack|Time
                    evtI = new EventInfo();
                    eventName = rdr.GetString(rdr.GetOrdinal("eventName"));
                    evtI.EventName = eventName;

                    //evtI.IdThread = rdr.GetString(rdr.GetOrdinal("idThread"));
                    if (rdr.IsDBNull(rdr.GetOrdinal("idThread")) == false) {
                        evtI.IdThread = rdr.GetString(rdr.GetOrdinal("idThread"));
                    }

                    if (rdr.IsDBNull(rdr.GetOrdinal("nameThread")) == false) {
                        evtI.NameThread = rdr.GetString(rdr.GetOrdinal("nameThread"));
                    }
                    if (rdr.IsDBNull(rdr.GetOrdinal("idFunction")) == false) {
                        evtI.IdFunction = rdr.GetString(rdr.GetOrdinal("idFunction"));
                    }
                    if (rdr.IsDBNull(rdr.GetOrdinal("nameFunction")) == false) {
                        evtI.NameFunction = rdr.GetString(rdr.GetOrdinal("nameFunction"));
                    }
                    if (rdr.IsDBNull(rdr.GetOrdinal("idClass")) == false) {
                        evtI.IdClass = rdr.GetString(rdr.GetOrdinal("idClass"));
                    }
                    if (rdr.IsDBNull(rdr.GetOrdinal("idObject")) == false) {
                        evtI.IdObject = rdr.GetString(rdr.GetOrdinal("idObject"));
                    }
                    if (rdr.IsDBNull(rdr.GetOrdinal("stackDepth")) == false) {
                        evtI.StackDepth = rdr.GetInt32(rdr.GetOrdinal("stackDepth"));
                    }
                    if (rdr.IsDBNull(rdr.GetOrdinal("exceptionName")) == false) {
                        evtI.ExceptionName = rdr.GetString(rdr.GetOrdinal("exceptionName"));
                    }


                    if (rdr.IsDBNull(rdr.GetOrdinal("static")) == false) {
                        evtI.IsStatic = rdr.GetBoolean(rdr.GetOrdinal("static"));
                    }
                    evtI.TimeStamp = rdr.GetString(rdr.GetOrdinal("timestamp"));
                    eventInfoList.Add(evtI);
                }
            } finally {
                // always call Close when done reading. 
                rdr.Close();
                // always call Close when done reading. 
                disconnect(connection);
            }
            disconnect(connection);
            return eventInfoList;
        }


        public List<DllModule> getDllModulesList(string str) {

            string queryStr;
            List<DllModule> dllModuleList = new List<DllModule>();
            ////queryStr = "select * from dllModules";
            //if (string.IsNullOrEmpty(str)) {//all
            //    queryStr = "select * from dllModules";
            //} else {
            //    queryStr = "select * from dllModules where name likes '" + str + "'";

            //}

            //SQLiteConnection dbConnection = connect();
            //SQLiteCommand cmd = new SQLiteCommand(queryStr, dbConnection);
            //SQLiteDataReader rdr = cmd.ExecuteReader();
            //DllModule dl = null;
            //try {
            //    while (rdr.Read()) {
            //        //Console.WriteLine(sqReader.GetInt32(0) + ", " + sqReader.GetString(1)); 
            //        dl = new DllModule();
            //        dl.Name = rdr.GetString(rdr.GetOrdinal("name"));
            //        dllModuleList.Add(dl);
            //    }
            //} finally {
            //    // always call Close when done reading. 
            //    rdr.Close();
            //    // always call Close when done reading. 
            //    disconnect(dbConnection);
            //}

            return dllModuleList;

        }


        public void loadFromFile() {
        
            SQLiteConnection dbConnection = connect();
            string date = String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now);
            string query = String.Format(@"insert into applications (""name"", ""path"", ""time"") VALUES (""WindowsApp"",""D:\Documents and Settings\Nicola\Desktop\CLRProfiler\WindowsApp\bin\Debug\"" ,""{0}"")", date);
            SQLiteCommand cmd = new SQLiteCommand(query, dbConnection);
            int rowsNumb = cmd.ExecuteNonQuery();

            //get the id of the last insert 
            query = "Select id  from applications where time in (select max(time) from applications)";
            cmd = new SQLiteCommand(query, dbConnection);
            SQLiteDataReader rdr = cmd.ExecuteReader();
            rdr.Read();
            int idApp = rdr.GetInt32(0);
            rdr.Dispose();

            //open the file
            string row = "";
            StreamReader fileRead = new StreamReader("outputMalna.log");
            while ((row = fileRead.ReadLine()) != null) {
                String[] tokenLocal;


                tokenLocal = row.Split('|');

                if (tokenLocal[0] == "CREA_THREAD") {
                    //|idThread|nomeThread |Time
                    query = String.Format(@"insert into events (""idApplication"", ""idThread"", ""nameThread"",""timestamp"",""eventId"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""1"")", idApp,tokenLocal[1],tokenLocal[2],tokenLocal[3]);
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
                if (tokenLocal[0] == "DESTROY_THREAD") {
                    //|idThread|Time
                    query = String.Format(@"insert into events (""idApplication"", ""idThread"", ""timestamp"",""eventId"") VALUES (""{0}"",""{1}"" ,""{2}"",""2"")", idApp, tokenLocal[1], tokenLocal[2]);
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
                if (tokenLocal[0] == "ENTER_FUNCTION") {
                    // |idFunction| nomeFunzione | idThread|  idClass	|	idObject	|stack	|	time
                   // query = String.Format(@"insert into events (""idApplication"", ""idFunction"", ""nameFunction"",""idThread"",""idClass"",""idObject"",""stackDepth"",""timestamp"",""eventId"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""{4}"",""{5}"",""{6}"",""{7}"",""3"")", idApp, tokenLocal[1], tokenLocal[2], tokenLocal[3], tokenLocal[4], tokenLocal[5], tokenLocal[6]);
                    if(tokenLocal[2].Contains("<static>")){
                        query = String.Format(@"insert into events (""idApplication"", ""idFunction"", ""nameFunction"",""idThread"",""idClass"",""idObject"",""stackDepth"",""timestamp"",""eventId"",""static"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""{4}"",""{5}"",""{6}"",""{7}"",""3"",""1"")", idApp, tokenLocal[1], tokenLocal[2], tokenLocal[3], tokenLocal[4], tokenLocal[5], tokenLocal[6],tokenLocal[7]);
                    }else{
                        query = String.Format(@"insert into events (""idApplication"", ""idFunction"", ""nameFunction"",""idThread"",""idClass"",""idObject"",""stackDepth"",""timestamp"",""eventId"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""{4}"",""{5}"",""{6}"",""{7}"",""3"")", idApp, tokenLocal[1], tokenLocal[2], tokenLocal[3], tokenLocal[4], tokenLocal[5], tokenLocal[6], tokenLocal[7]);
                    }
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
                if (tokenLocal[0] == "LEAVE_FUNCTION") {
                    //TODO Missing idClass
                   // |	idFunction|	nomeFunzione |	idThread |idObject	|	stack	|	time//
                    if (tokenLocal[2].Contains("<static>")) {
                        query = String.Format(@"insert into events (""idApplication"", ""idFunction"", ""nameFunction"",""idThread"",""idObject"",""stackDepth"",""timestamp"",""eventId"",""static"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""{4}"",""{5}"",""{6}"",""4"",""1"")", idApp, tokenLocal[1], tokenLocal[2], tokenLocal[3], tokenLocal[4], tokenLocal[5], tokenLocal[6]);
                    } else {
                        query = String.Format(@"insert into events (""idApplication"", ""idFunction"", ""nameFunction"",""idThread"",""idObject"",""stackDepth"",""timestamp"",""eventId"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""{4}"",""{5}"",""{6}"",""4"")", idApp, tokenLocal[1], tokenLocal[2], tokenLocal[3], tokenLocal[4], tokenLocal[5], tokenLocal[6]);
                    }
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
                if (tokenLocal[0] == "EXCEPTION_THROWN") {
                    ////EVENTO			|	eccezione			|	idThread	|	idObj		|Stack	|	Time
                    //EXCEPTION_THROWN|System.ArithmeticException|1560584|19678508|6|3876204378
                    query = String.Format(@"insert into events (""idApplication"", ""exceptionName"",""idThread"",""idObject"",""stackDepth"",""timestamp"",""eventId"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""{4}"",""{5}"",""5"")", idApp, tokenLocal[1], tokenLocal[2], tokenLocal[3], tokenLocal[4], tokenLocal[5]);
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
                if (tokenLocal[0] == "EXCEPTION_CATCHER_ENTER") {
                    ////EVENTO					|	idThread	|	idObj		|stack	|	Time
                    query = String.Format(@"insert into events (""idApplication"",""idThread"",""idObject"",""stackDepth"",""timestamp"",""eventId"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""{4}"",""6"")", idApp, tokenLocal[1], tokenLocal[2], tokenLocal[3], tokenLocal[4]);
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                } if (tokenLocal[0] == "EXCEPTION_UNWIND_LEAVE_FUNCTION") {
                    //EVENTO						|	idThread	|stack	|	Time
                    query = String.Format(@"insert into events (""idApplication"",""idThread"",""stackDepth"",""timestamp"",""eventId"") VALUES (""{0}"",""{1}"" ,""{2}"",""{3}"",""7"")", idApp, tokenLocal[1], tokenLocal[2], tokenLocal[3]);
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
                if (tokenLocal[0] == "SHUTDOWN") {
                    // |Time
                    query = String.Format(@"insert into events (""idApplication"", ""timestamp"",""eventId"") VALUES (""{0}"",""{1}"", ""8"")", idApp, tokenLocal[1]);
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }

            }//End while lettura file
            fileRead.Close();

            disconnect(dbConnection);
        
        }



        internal List<ApplicationInfo> getApplicationInfo() {

            string queryStr = "select * from applications order by time desc";
            List<ApplicationInfo> appInfoList = new List<ApplicationInfo>();
            

            SQLiteConnection dbConnection = connect();
            SQLiteCommand cmd = new SQLiteCommand(queryStr, dbConnection);
            SQLiteDataReader rdr = cmd.ExecuteReader();
            ApplicationInfo appInfo = null;
            try {
                while (rdr.Read()) {
                    appInfo = new ApplicationInfo();
                    appInfo.Id = rdr.GetInt32(rdr.GetOrdinal("id"));
                    appInfo.Name = rdr.GetString(rdr.GetOrdinal("name"));
                    appInfo.Path = rdr.GetString(rdr.GetOrdinal("path"));
                    appInfo.Time = rdr.GetString(rdr.GetOrdinal("time"));
                    appInfoList.Add(appInfo);
                }
            } finally {
                // always call Close when done reading. 
                rdr.Close();
                // always call Close when done reading. 
                disconnect(dbConnection);
            }
            return appInfoList;
        }
    }
}
